﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using EasyLibrary.AR.Classes;

namespace EasyLibrary.AR
{
    public abstract class ActiveRecord
    {
        private static readonly string _CONNSTR = ConnectionString.GetUsingHostname();

        private static SqlTransaction myTx;
        private static SqlConnection myCnx;
        protected int myID;

        public int ID
        {
            get { return myID; }
        }

        private static string CONNSTR
        {
            get
            {
                //return System.Configuration.ConfigurationSettings.AppSettings["ConnStr"];
                return _CONNSTR;
            }
        }

        protected static SqlTransaction CurrentTransaction
        {
            get { return myTx; }
        }

        public abstract void Save();

        protected static SqlConnection GetConnection(bool open)
        {
            var cnx = new SqlConnection(CONNSTR);
            if (open)
                cnx.Open();
            return cnx;
        }

        protected static DataSet ExecuteQuery(SqlConnection cnx, string sql)
        {
            try
            {
                var da = new SqlDataAdapter(sql, cnx);
                var ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch (SqlException ex)
            {
                throw new ApplicationException("Erro BD", ex);
            }
        }

        protected static int ExecuteNonQuery(SqlConnection cnx, string sql)
        {
            var cmd = new SqlCommand(sql, cnx);
            return cmd.ExecuteNonQuery();
        }

        protected static int ExecuteNonQuery(SqlTransaction tx, string sql)
        {
            var cmd = new SqlCommand(sql, tx.Connection, tx);
            return cmd.ExecuteNonQuery();
        }

        protected static int ExecuteNonQuery(SqlTransaction tx, SqlCommand cmd)
        {
            try
            {
                if (myCnx == null)
                    myCnx = GetConnection(false);
                cmd.Connection = myCnx;
                myCnx.Open();
               
                cmd.Transaction = tx;
                int r = cmd.ExecuteNonQuery();
                myCnx.Close();
                return r;
               
            } catch(SqlException ex)
            {
                throw new ApplicationException("Erro BD", ex);
            }

        }

        protected static DataSet ExecuteQuery(string sql)
        {
            try
            {
                if (myCnx == null)
                    myCnx = GetConnection(false);

                var da = new SqlDataAdapter(sql, myCnx);
                var ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch (SqlException ex)
            {
                throw new ApplicationException("Erro BD", ex);
            }
        }

        protected static DataSet ExecuteTransactedQuery(string sql)
        {
            try
            {
                var da = new SqlDataAdapter(sql, myCnx);
                var ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch (SqlException ex)
            {
                throw new ApplicationException("Erro BD", ex);
            }
        }

        protected static int ExecuteNonQuery(string sql)
        {
            SqlConnection cnx = GetConnection(true);
            int r = ExecuteNonQuery(cnx, sql);
            cnx.Close();
            return r;
        }

        protected static int ExecuteTransactedNonQuery(string sql)
        {
            var cmd = new SqlCommand(sql);
            return ExecuteTransactedNonQuery(cmd);
        }

        protected static int ExecuteTransactedNonQuery(SqlCommand cmd)
        {
            try
            {
                cmd.Transaction = CurrentTransaction;
                cmd.Connection = CurrentTransaction.Connection;
                return cmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                // debug purposes only!!!
                throw ex;
            }
        }

        protected static void BeginTransaction()
        {
            try
            {
                if (myTx == null)
                    myTx = GetConnection(true).BeginTransaction();
            }
            catch (SqlException ex)
            {
                throw new ApplicationException("Erro BD", ex);
            }
        }

        protected static void CommitTransaction()
        {
            if (myTx != null)
            {
                SqlConnection cnx = myTx.Connection;
                myTx.Commit();
                cnx.Close();
            }
        }

        protected static void RoolbackTransaction()
        {
            if (myTx != null)
            {
                SqlConnection cnx = myTx.Connection;
                myTx.Rollback();
                cnx.Close();
            }
        }

        public DataSet executeCommandOnDataSet(string query)
        {
            try
            {
                if (myCnx == null)
                    myCnx = GetConnection(false);


                using (var sp = new SqlCommand(query, myCnx) {CommandType = CommandType.Text, CommandTimeout = 3200})
                {
                    myCnx.Open();
                    var dataAdapter = new SqlDataAdapter(sp);
                    var resultDataSet = new DataSet {Locale = CultureInfo.InvariantCulture};
                    dataAdapter.Fill(resultDataSet);
                    myCnx.Close();
                    return resultDataSet;
                }
            }
            catch (SqlException ex)
            {
                throw new ApplicationException("Erro BD", ex);
            }
        }
    }
}